﻿using System;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;


namespace FileUtility
{
    //Did not Create Test Class for this Class. 
    public class SqlDataBaseExtender:SqlDatabase 
    {
          /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        public SqlDataBaseExtender(string connectionString)
            : base(connectionString)
        { }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string storedProcedureName, NameValueCollection parameters)
        {
            using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameters))
            {
                return ExecuteDataSet(command);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(DbTransaction transaction,string storedProcedureName, NameValueCollection parameters)
        {
            using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameters))
            {
                return ExecuteDataSet(command, transaction);
            }
        }
         /// <summary>
         /// 
         /// </summary>
         /// <param name="transaction"></param>
         /// <param name="storedProcedureName"></param>
         /// <param name="parameters"></param>
         /// <param name="timeOut"></param>
         /// <returns></returns>
        public DataSet ExecuteDataSet(DbTransaction transaction, string storedProcedureName, NameValueCollection parameters,int timeOut)
        {
            using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameters))
            {
                command.CommandTimeout = timeOut;
                return ExecuteDataSet(command, transaction);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="parameterValues"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(DbTransaction transaction, string storedProcedureName, NameValueCollection parameterValues)
        {
            using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                return ExecuteNonQuery(command, transaction);
            }
        }

        public int ExecuteNonQuery(DbTransaction transaction, string storedProcedureName, NameValueCollection parameterValues,int timeOut)
        {
            using (DbCommand command = GetStoredProcCommand(storedProcedureName, parameterValues))
            {
                command.CommandTimeout = timeOut;
                return ExecuteNonQuery(command, transaction);
            }
        }

        public DbCommand GetStoredProcCommand(string storedProcedureName, NameValueCollection parameters)
        {
            DbCommand command = GetStoredProcCommand(storedProcedureName);
            this.DiscoverParameters(command);
            this.AssignParameterValues(command, parameters);
            return command;
        }

        private void AssignParameterValues(DbCommand command, NameValueCollection values)
        {
            int parameterIndexShift = UserParametersStartIndex();	// DONE magic number, depends on the database
            object pv = null;
            for (int i = parameterIndexShift; i < command.Parameters.Count; i++)
            {
                IDataParameter parameter = command.Parameters[i];
                pv = ConvertValueByDbType(parameter.DbType, values[parameter.ParameterName.Substring(1)]);
                if (pv == null)
                    continue;
                SetParameterValue(command, parameter.ParameterName, pv);
            }
           
        }

        internal static object ConvertValueByDbType(DbType dbType, string value)
        {
            if (value == null || string.Equals(value, "@NULL@", StringComparison.InvariantCultureIgnoreCase))
                return (object)null;

            if (dbType == DbType.StringFixedLength || dbType == DbType.AnsiStringFixedLength || dbType == DbType.AnsiString || dbType == DbType.String || dbType == DbType.Object)
            {
                return value;
            }
            else
            {
                if (string.IsNullOrEmpty(value.Trim()))
                    return (object)null;
                else
                {
                    switch (dbType)
                    {
                        case DbType.Guid:
                            return new Guid(value);
                        case DbType.Int32:
                        case DbType.Int16:
                            return int.Parse(value);
                        case DbType.SByte:
                            return System.SByte.Parse(value);
                        case DbType.Byte:
                            return byte.Parse(value);
                        case DbType.Int64:
                            return System.Int64.Parse(value);
                        case DbType.Binary:
                            try
                            {
                                byte[] b = System.Convert.FromBase64String(value);
                                return b;
                            }
                            catch
                            {
                                throw new InvalidCastException("Invalid base64 String,Cant convert from the string to Binary");
                            }

                        case DbType.Boolean:
                            string v = value.Trim();
                            if (string.Equals(v, "1", StringComparison.InvariantCultureIgnoreCase) || string.Equals(v, "true", StringComparison.InvariantCultureIgnoreCase))
                                return true;
                            else
                                return false;
                        case DbType.Currency:
                        case DbType.VarNumeric:
                        case DbType.Double:
                            return Double.Parse(value);
                        case DbType.Single:
                            return float.Parse(value);
                        case DbType.Decimal:
                            return Decimal.Parse(value);
                        case DbType.Date:
                        case DbType.DateTime:
                        case DbType.DateTime2:
                        case DbType.Time:
                            return DateTime.Parse(value);
                        case DbType.UInt16:
                            return UInt16.Parse(value);
                        case DbType.UInt32:
                            return UInt32.Parse(value);
                        case DbType.UInt64:
                            return UInt64.Parse(value);
                        default:
                            return value;
                    }
                }
            }
        }

        public DbConnection GetOpenConnetion()
        {
            return base.GetOpenConnection().Connection;
        }
    }
}
